import os.path

from conf.database import Database
from conf.settings import Settings


class ManHourSummaryTable:
    def __init__(self, settings=None, filepath=None):
        self.workbook = None
        self.TAG = '【工时汇总表】'
        self.settings = settings
        self.filepath = filepath

        if filepath is None:
            print(self.TAG, f'找不到文件【{self.filepath}】')
            return

        print(self.TAG, self.filepath)

        db_file_path = os.path.join(settings.db_path, settings.db_file)
        db_connect = Database.connect(db_file_path)
        drop_sql = 'drop table if exists man_hour_summary_table'
        Database.execute(db_connect=db_connect, sql=drop_sql)
        create_sql = 'create table if not exists man_hour_summary_table\
        (id INTEGER PRIMARY KEY,project_name TEXT,project_code TEXT,project_member TEXT,\
        year INTEGER,month INTEGER,retained_hour FLOAT,update_time DATETIME)'
        Database.execute(db_connect=db_connect, sql=create_sql)
        Database.close(db_connect)

    def error(self, message):
        self.settings.logger.error(message)

    def load(self, workbook):
        self.workbook = workbook

    def show(self):
        sheets = self.workbook.sheets
        sheet = sheets(1)

        rows = sheet.used_range.value
        for cols in rows:
            for cell in cols:
                print(cell, end=' ')
            print('')

    def close(self):
        self.workbook.close()

    def exec(self):
        db_file_path = os.path.join(self.settings.db_path, self.settings.db_file)
        db_connect = Database.connect(db_file=db_file_path)
        sheets = self.workbook.sheets
        sheet = sheets(1)
        rows = sheet.used_range.value
        insert_sql = 'insert into man_hour_summary_table\
        (project_name,project_code,project_member,year,month,retained_hour,update_time) values (?,?,?,?,?,?,?)'
        insert_data = [(rows[1:][index][0], rows[1:][index][1], rows[1:][index][2],
                        rows[1:][index][3], rows[1:][index][4], rows[1:][index][7], Settings.get_datetime())
                       for index in range(len(rows[1:]))]
        Database.executemany(db_connect=db_connect, sql=insert_sql, data=insert_data)
        Database.close(db_connect=db_connect)
        pass
